﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using ChungNhanSinhVien.Models;
using ChungNhanSinhVien.Entities;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
using CrystalDecisions.CrystalReports.Engine;
using System.IO;
using System.Web.UI;
using System.Text;
using System.Data.OleDb;
using ChungNhanSinhVien.Infrastructures;
using ChungNhanSinhVien.Report;



namespace ChungNhanSinhVien.Controllers
{
    public class Ad_YeuCauController : Controller
    {
        //
        // GET: /Ad_LayYeuCau/

        public ActionResult Index()
        {
            return View();
        }

        public ActionResult LayYeuCau()
        {
            if (SessionManager.nsr == null)
            {
                return RedirectToAction("Index", "DangNhap");
            }
            else
            {
                return View();
            }
        }

        [HttpPost]
        public ActionResult LayYeuCau(YeuCauModel model)
        {
            if (SessionManager.nsr == null)
            {
                return RedirectToAction("Index", "DangNhap");
            }
            else
            {
                var action = Request.Params.AllKeys.FirstOrDefault(key => key.Equals("ExportToExcel"));
                if (action.Equals("ExportToExcel"))
                {
                    YeuCauRepository ycRepo = new YeuCauRepository();
                    DataTimeModel datetimeModel = new DataTimeModel();
                    DateTime ngaybatdau = new DateTime(), ngaycuoi = new DateTime();
                    if (model.ngaybatdau != null)
                    {
                        if (datetimeModel.ChuyenThanhNgayTu_ddmmyyy(model.ngaybatdau, out ngaybatdau))
                        {
                            TempData["ngaybatdau"] = "ok";
                        }
                        else
                        {
                            TempData["ngaybatdau"] = "sai";
                            return View();
                        }
                    }
                    if (model.ngaycuoi != null)
                    {
                        if (datetimeModel.ChuyenThanhNgayTu_ddmmyyy(model.ngaycuoi, out ngaycuoi))
                        {
                            TempData["ngaycuoi"] = "ok";
                        }
                        else
                        {
                            TempData["ngaycuoi"] = "sai";
                            return View();
                        }
                    }

                    if (model.soluongyeucau.CompareTo(0) <= 0)
                    {
                        TempData["soluong"] = "sai";
                        model.msb_soluong = "sai";
                        return View();
                    }

                    var list = new List<YeuCau>();
                    if (TempData["ngaybatdau"].ToString().Equals("ok") && TempData["ngaycuoi"].ToString().Equals("ok"))
                    {
                        var yeucaus = new List<YeuCau>();
                        yeucaus = ycRepo.LayYeuCausTheoNgayDauCuoi(ngaybatdau, ngaycuoi, model.soluongyeucau);

                        if (yeucaus.Count.Equals(0))
                        {
                            TempData["soluong"] = "emptyYC";
                            model.msb_soluong = "emptyYC";
                            return View();
                        }

                        List<YeuCauReport> ycRps = new List<YeuCauReport>();
                        foreach (var item in yeucaus)
                        {
                            YeuCauReport yc = new YeuCauReport()
                            {
                                ID = item.ID,
                                MaCN = item.ChungNhanID.Value,
                                StudentID = item.StudentID,
                                MaBieuMau = item.ChungNhan.BieuMauID.Value
                            };
                            ycRps.Add(yc);
                        }
                        //var grid = new GridView();
                        //grid.DataSource = from p in yeucaus
                        //                  select new
                        //                  {
                        //                      MaYC = p.ID,
                        //                      MaSV = p.StudentID,
                        //                      //MaLoaiCN = p.ChungNhan.LoaiChungNhanID,
                        //                      //LoaiChungNhan = p.ChungNhan.LoaiChungNhan.TenLoaiChungNhan,
                        //                      //MaCN = p.ChungNhanID,
                        //                      //ChungNhan = p.ChungNhan.TenChungNhan,
                        //                      //NgayYeuCau = p.NgayYeuCau.Value.Day + "/" + p.NgayYeuCau.Value.Month + "/" + p.NgayYeuCau.Value.Year,
                        //                      //TrangThai = p.TrangThai
                        //                  };
                        //grid.DataBind();

                        Response.ClearContent();

                        #region Export Excel - Sinh
                        //string namefile = (DateTime.Now.Year + "" + DateTime.Now.Month + "" + DateTime.Now.Day + "" + DateTime.Now.Millisecond);
                        ////Response.AddHeader("content-disposition", "attachment; filename=" + namefile + ".xls");                    
                        ////Response.ContentType = "application/excel";

                        ////Response.ContentType = "text/html";
                        ////Response.AddHeader("Content-Type", "text/html;charset=UTF-8");

                        //Response.AddHeader("content-disposition", "attachment; filename=" + namefile + ".xls");
                        //Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                        ////Response.ContentEncoding = Encoding.GetEncoding("Windows-1252");
                        ////Response.Charset = "UTF-8";

                        //StringWriter sw = new StringWriter();
                        //HtmlTextWriter htw = new HtmlTextWriter(sw);

                        //grid.RenderControl(htw);

                        //Response.Write(sw.ToString());

                        //Response.End();
                        #endregion

                        ycRepo.ChuyenTrangThaiChoYeuCauCuaDSSV(yeucaus, "Đang xử lý");
                        new NhatKyXuLyYeuCauRepository().ThemNhieu(yeucaus, "Lấy yêu cầu để xử lý");
                        return this.ExportToExcel(ycRps);
                        //                        return View("LayYeuCau");
                    }
                }
                return View("LayYeuCau");
            }

        }

        [ChildActionOnly]
        public ActionResult UC_DieuKienLayYeuCau()
        {
            if (SessionManager.nsr == null)
            {
                return RedirectToAction("Index", "DangNhap");
            }
            else
            {
                YeuCauModel model = new YeuCauModel();
                model.ngaycuoi = (DateTime.Today.Day) + "/" + DateTime.Today.Month + "/" + DateTime.Today.Year;
                DateTime ngaybatdau = DateTime.Today.Date.AddDays(-1);
                model.ngaybatdau = ngaybatdau.Day + "/" + ngaybatdau.Month + "/" + ngaybatdau.Year;

                model.soluongyeucau = new YeuCauRepository().DemSoLuongYeuCauTheoTrangThai("Chờ xử lý");
                //YeuCauModel model = new YeuCauModel();
                model.msb_ngaybatdau = TempData["ngaybatdau"] as string ?? string.Empty;
                model.msb_ngaycuoi = TempData["ngaycuoi"] as string ?? string.Empty;
                model.msb_soluong = TempData["soluong"] as string ?? string.Empty;
                return PartialView(model);
            }
        }

        [ChildActionOnly]
        public ActionResult UC_KetQuaGhiNhan()
        {
            if (SessionManager.nsr == null)
            {
                return RedirectToAction("Index", "DangNhap");
            }
            else
            {
                ViewData["soluong"] = "";
                YeuCauRepository ycRepo = new YeuCauRepository();
                //var list = ycRepo.LayYeuCausMoiNhat(100);
                var list = ycRepo.LayTatCaYeuCauMoiNhat();
                return PartialView(list);
            }
        }

        public ActionResult CapNhatKetQuaXuLy()
        {
            if (SessionManager.nsr == null)
            {
                return RedirectToAction("Index", "DangNhap");
            }
            else
            {
                ViewData["tb"] = TempData["tb"] as string ?? string.Empty;
                return View();
            }
        }

        [HttpPost]
        public ActionResult CapNhatKetQuaXuLy(YeuCauModel model, FormCollection collection, HttpPostedFileBase FileImport)
        {
            if (SessionManager.nsr == null)
            {
                return RedirectToAction("Index", "DangNhap");
            }
            else
            {
                TempData["tb"] = "";
                if (FileImport == null)
                {
                    TempData["tb"] = "ChuaChonFile";
                    return RedirectToAction("CapNhatKetQuaXuLy");
                }
                #region Kiểm tra có file excel đang thực thi
                bool flagExcel = false;
                System.Diagnostics.Process[] procs = System.Diagnostics.Process.GetProcesses();
                for (int j = 0; j < procs.Length; j++)
                {
                    if (procs[j].ProcessName.Equals("EXCEL"))
                    {
                        flagExcel = true;
                        break;
                    }
                }

                if (flagExcel)
                {
                    TempData["tb"] = "TonTaiExcelDangThucThi";
                    return RedirectToAction("CapNhatKetQuaXuLy");
                }
                #endregion

                string extendFile = Path.GetExtension(FileImport.FileName);
                string path = Server.MapPath("~/Content/TempUploadFiles/" + FileImport.FileName);

                //upload file len server       
                FileImport.SaveAs(path);

                string excelConnectionString = "";

                var btncapnhat = Request.Params.AllKeys.FirstOrDefault(key => key.Equals("CapNhatKetQuaXuLy"));
                if (btncapnhat != null && !btncapnhat.Equals(""))
                {
                    if (!path.Replace(" ", "").Equals(""))
                    {
                        if (extendFile.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase))
                        {
                            excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""; //getExcelFileName
                        }
                        else if (extendFile.Equals(".xls", StringComparison.CurrentCultureIgnoreCase))
                        {
                            excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""";
                        }

                        using (OleDbConnection oleDbConnection = new OleDbConnection(string.Format(excelConnectionString, path)))
                        {
                            try
                            {
                                oleDbConnection.Open();
                            }
                            catch
                            {
                                TempData["tb"] = "LoiMoKetNoi";
                                return RedirectToAction("CapNhatKetQuaXuLy");
                            }
                            DataTable dataSchema = oleDbConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);//return detail of sheets in excel
                            DataTable _dt = dataSchema;
                            string selectSheet = "";
                            foreach (DataRow item in _dt.Rows)
                            {
                                if (selectSheet.Contains("_xlnm#_FilterDatabase"))
                                {
                                    TempData["tb"] = "LoiFilter";
                                    return RedirectToAction("CapNhatKetQuaXuLy");
                                }
                                selectSheet = item.ItemArray[2].ToString();
                                break;
                            }

                            DataTable dataTable = new SharedFunc().GetDataExcel(oleDbConnection, selectSheet);
                            if (dataTable == null)
                            {
                                TempData["tb"] = "TapTinKhongTonTai";
                                return RedirectToAction("CapNhatKetQuaXuLy");
                            }

                            #region Add tên cột cần import
                            List<string> colExcelList = new List<string>();
                            colExcelList.Add("MaYC");
                            //colExcelList.Add("MaSV");
                            //colExcelList.Add("MaCN");
                            colExcelList.Add("TrangThai");
                            colExcelList.Add("GhiChu");
                            string[] colExcel = colExcelList.ToArray();
                            #endregion
                            DataTable dataAfterRemove = new DataTable();
                            int MaxValue = 0;
                            try
                            {
                                dataAfterRemove = new SharedFunc().removeDataDupExcel(dataTable, colExcel);
                                MaxValue = dataAfterRemove.Rows.Count;
                            }
                            catch
                            {
                                TempData["tb"] = "LoiDuLieuExcel";
                                return RedirectToAction("CapNhatKetQuaXuLy");
                            }
                            #region Quy trình import
                            int countItem = 0;
                            foreach (DataRow item in dataAfterRemove.Rows)
                            {
                                if (countItem < MaxValue)
                                {
                                    YeuCau yc = new YeuCau();
                                    yc.ID = int.Parse(item[colExcel.ElementAt(colExcel.ToList().IndexOf("MaYC"))].ToString());
                                    //yc.StudentID = item[colExcel.ElementAt(colExcel.ToList().IndexOf("MaSV"))].ToString();
                                    //yc.ChungNhanID = int.Parse(item[colExcel.ElementAt(colExcel.ToList().IndexOf("MaCN"))].ToString());
                                    string trangthai = item[colExcel.ElementAt(colExcel.ToList().IndexOf("TrangThai"))].ToString();
                                    if (trangthai.Equals("3"))
                                        yc.TrangThai = "Hợp lệ";
                                    if (trangthai.Equals("4"))
                                        yc.TrangThai = "Không hợp lệ";
                                    yc.GhiChu = item[colExcel.ElementAt(colExcel.ToList().IndexOf("GhiChu"))].ToString();
                                    new YeuCauRepository().CapNhatYeuCau(yc);
                                    countItem++;
                                }
                            }
                            TempData["tb"] = "HoanTatCapNhat";
                            #endregion
                        }
                    }
                }
                return RedirectToAction("CapNhatKetQuaXuLy");
            }
        }

        public ActionResult GhiTraYeuCau(YeuCauModel model)
        {
            if (SessionManager.nsr != null)
            {
                //if (model.maSV == null)
                //    ViewData["studentID"] = "";
                //else
                //    ViewData["studentID"] = model.maSV;                
                
                return View();
            }
            else
                return RedirectToAction("Index", "DangNhap");
        }

        [ChildActionOnly]
        public ActionResult UC_DieuKienGhiTraYeuCau()
        {
            return PartialView();
        }

        [ChildActionOnly]
        public ActionResult UC_KetQuaGhiTraYeuCau(YeuCauModel model)
        {
            ViewData["tb"] = "";
            if (model != null)
            {
                if (model.maSV != null)
                {
                    StudentRepository studentRepo = new StudentRepository();
                    if (!studentRepo.KiemTraTaiKhoanDaTonTaiTheoMaSV(model.maSV))
                    {
                        ViewData["tb"] = "svkotontai";
                        return PartialView();
                    }

                    var yeucaus = new List<YeuCau>();
                    YeuCauRepository yeucauRepo = new YeuCauRepository();
                    yeucaus = yeucauRepo.LayYeuCausTheoMaSVTrangThai(model.maSV, 3);
                    if (yeucaus.Count.Equals(0))
                    {
                        ViewData["tb"] = "kocoyeucau";
                        return PartialView();
                    }
                    else
                    {
                        yeucauRepo.CapNhatTrangThaiYeuCausThanhDaGhiTra(yeucaus);
                        ViewData["tb"] = "coyeucau";
                        return PartialView(yeucaus);
                    }                    
                }
                return PartialView();
            }
            else
                return PartialView();
        }

        FileStreamResult ExportToExcel(List<YeuCauReport> datasource)
        {
            ReportClass rptARR = new ReportClass();

            rptARR.FileName = Server.MapPath("/Report/YeuCauRp.rpt");
            rptARR.Load();
            //datareport has ienumerable type
            rptARR.SetDataSource(datasource);

            //rptARR.ExportToHttpResponse(ExportFormatType.Excel, System.Web.HttpContext.Current.Response, false, string.Format("ds_dk_bang_diem_{0:ddMMyyyy}.xls", DateTime.Today));
            Stream reportStream = rptARR.ExportToStream(CrystalDecisions.Shared.ExportFormatType.Excel);

            Response.ClearContent();
            Response.ClearHeaders();
            Response.AppendHeader("charset", "utf-8");

            rptARR.Close();
            rptARR.Dispose();

            string namefile = "{0: yyyyMMddfff}.xls";

            return File(reportStream, "application/xls", string.Format(namefile, DateTime.Now));
        }

    }
}
